﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccessLibrary;

namespace BusinessLayer
{
    public class CLsSalaryProcess
    {

        public int SalaryProcessID { get; set; }
        public string ProcessName { get; set; }
        public DateTime? FromDate { get; set; }
        public DateTime? ToDate { get; set; }

        public DataTable GetAllSalaryProcess()
        {
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable("SELECT * FROM  SalaryProcess", "SalaryProcess");
            }
        }

        public void GetSalaryProcess(int intSalaryProcessID)
        {
            using (OleDataClient data = new OleDataClient())
            {
                DataTable dt = data.GetDataTable("Select * from SalaryProcess where SalaryProcessID=" + intSalaryProcessID.ToString(), "SalaryProcess");
                if (dt.Rows.Count > 0)
                {
                    SalaryProcessID = Convert.ToInt32(dt.Rows[0]["SalaryProcessID"]);
                    ProcessName = dt.Rows[0]["ProcessName"].ToString();
                    FromDate = Convert.ToDateTime(dt.Rows[0]["FromDate"]);
                    ToDate = Convert.ToDateTime(dt.Rows[0]["ToDate"]);
                }
            }
        }

        public bool SaveSalaryProcess(string KeyForSave)
        {
            using (OleDataClient data = new OleDataClient())
            {
                if (KeyForSave == "add")
                {
                    string query = "select count(*) from SalaryProcess where ProcessName='" + ProcessName + "'";
                    string count = "";
                    count = data.GetValue(query);
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Salary Process already exist");
                    }
                    query = "select count(*)  from SalaryProcess  "
                                  + "where((Format([FromDate],'yyyy-mm-dd')  <=  '" + Utility.GetDateYYYYMMDD(FromDate) + "'  and  Format([ToDate],'yyyy-mm-dd')  >= '" + Utility.GetDateYYYYMMDD(FromDate) + "' )"
                                  + "or"
                                  + "(Format([FromDate],'yyyy-mm-dd')  <=  '" + Utility.GetDateYYYYMMDD(ToDate) + "'  and  Format([ToDate],'yyyy-mm-dd')  >= '" + Utility.GetDateYYYYMMDD(ToDate) + "' ))";
                    count = data.GetValue(query);
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Salary Process already exist for given date");
                    }
                    data.ExecuteNonQuery("Insert into SalaryProcess (ProcessName,FromDate,ToDate) values('" + ProcessName.ToString() + "','" + FromDate + "','" + ToDate + "')");
                }
                else
                {
                    string query = "select count(*) from SalaryProcess where ProcessName='" + ProcessName + "' and SalaryProcessID NOT IN (" + SalaryProcessID.ToString() + ")";
                    string count = data.GetValue(query);
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Salary Process name already exists.");
                    }
                    data.ExecuteNonQuery("Update SalaryProcess set ProcessName = '" + ProcessName + "'   where SalaryProcessID=" + SalaryProcessID.ToString());
                }
            }
            return true;
        }

        public void DeleteSalaryProcess()
        {
            using (OleDataClient data = new OleDataClient())
            {
                data.StartTran();
                try
                {
                    data.ExecuteNonQuery("Delete from  SalaryProcess where SalaryProcessID=" + SalaryProcessID.ToString());
                    ClsSalaryGeneration objClsSalaryGeneration = new ClsSalaryGeneration();
                    objClsSalaryGeneration.UndoGenerateAllEmployee(SalaryProcessID);
                    data.CommitTran();
                }
                catch (Exception ex)
                {
                    data.RollBackTran();
                    throw ex;
                }
            }
        }
    }
}

